package 图书管理系统;

import java.sql.*;
import java.util.Scanner;



public class ConnectMysql {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/图书管理系统?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";


    // 数据库的用户名与密码，需要根据自己的设置
    static final String USER = "root";
    static final String PASS = "lin2002";

    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        Statement stmt = null;

        // 注册 JDBC 驱动
        try {
            Class.forName(JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        // 打开链接
//        System.out.println("连接数据库...");
        try {
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
//            System.out.println(" 实例化Statement对象...");
            // 执行查询
            stmt = conn.createStatement();
            System.out.println("\033[35;6m" + "欢迎来到图书管理系统" + "\033[0m");
            while (true){
                System.out.println();
//                System.out.println();
                System.out.println("\033[35;6m" + "1.查看所有图书信息" + "\033[0m");
                System.out.println("\033[35;6m" + "2.添加图书信息" + "\033[0m");
                System.out.println("\033[35;6m" + "3.删除图书信息" + "\033[0m");
                System.out.println("\033[35;6m" + "4.修改图书信息" + "\033[0m");
                System.out.println("\033[35;6m" + "5.借阅图书" + "\033[0m");
                System.out.println("\033[35;6m" + "6.归还图书" + "\033[0m");
                System.out.println("\033[35;6m" + "7.退出系统" + "\033[0m");
//                System.out.println("7.退出系统");
                int n;
                System.out.println("请输入你的操作：");
                Scanner sc = new Scanner(System.in);
                n = sc.nextInt();
                switch (n) {
                    case 1 -> lookBook(stmt);
                    case 2 -> addBook(stmt);
                    case 3 -> deleteBook(stmt);
                    case 4 -> updateBook(stmt);
                    case 5 -> borrowBook(stmt);
                    case 6 -> repayBook(stmt);
                    case 7 -> {
                        System.out.println("\033[34;1m" + "谢谢使用" + "\033[0m");
                        return;
                    }
                }

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            assert stmt != null;
            stmt.close();
            conn.close();
        }


    }



    public static void lookBook(Statement stmt) throws SQLException {
        String sql;
        sql = "SELECT name, author, price,statm FROM 图书管理";
        ResultSet rs = stmt.executeQuery(sql);
        while(rs.next()) {
            // 通过字段检索
            String name = rs.getString("name");
            String author = rs.getString("author");
            String price = rs.getString("price");
            String statm = rs.getString("statm");

            // 输出数据
            System.out.print("书名:" + "《"+name+"》");
            System.out.print("\t作者:" + author);
            System.out.print("\t价格:" + price);
            System.out.print("\t状态:" + statm);
            System.out.print("\n");
        }
        rs.close();
    }

    public static void addBook(Statement stmt){
        System.out.println("请输入书名:");
        Scanner scn = new Scanner(System.in);
        String name = scn.nextLine();
//        b.setName(scn.nextLine());
        System.out.println("请输入作者:");
//        b.setAuthor(sca.nextLine());
        String author = scn.nextLine();
        System.out.println("请输入图书价格:");
//        Scanner scp = new Scanner(System.in);
        String price = scn.nextLine();
//        b.setPrice(scp.nextLine());


        String sql = "INSERT INTO `图书管理`(name,author,price,statm)value('"+name+"','"+author+"','"+price+"','未借出')";
        int i = 0;
        try {
            i = stmt.executeUpdate(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        }
//        finally {
//            close();
//        }
        if (i==0){
            System.out.println("\033[31;6m" + "添加失败" + "\033[0m");
//            System.out.println("添加失败");
        }else{
            System.out.println("\033[36;6m" + "添加成功" + "\033[0m");
//            System.out.println("添加成功");
        }

    }

    public static void deleteBook(Statement stmt){
        System.out.println("请输入要删除的图书名：");
        Scanner sc = new Scanner(System.in);
        String name = sc.nextLine();
        String sql = "delete from 图书管理 where name = '"+name+"'";
        int i = 0;
        try {
            i = stmt.executeUpdate(sql);


        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (i==0){
            System.out.println("\033[31;6m" + "删除失败" + "\033[0m");
//            System.out.println("删除失败");
        }else{
            System.out.println("\033[36;6m" + "删除成功" + "\033[0m");
//            System.out.println("删除成功");
        }

    }

    public static void updateBook(Statement stmt){
        System.out.println("请输入要修改的图书名：");
        Scanner sc = new Scanner(System.in);
        String name = sc.nextLine();
        String sql = "delete from 图书管理 where name = '"+name+"'";
//        String sql1 = "update"
        int i = 0;
        try {
            i = stmt.executeUpdate(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (i==0){
            System.out.println("输入图书名有误！");
            return ;
        }
        System.out.println("请输入修改后书名:");
        Scanner scn = new Scanner(System.in);
        String name1 = scn.nextLine();
//        b.setName(scn.nextLine());
        System.out.println("请输入修改后作者:");
//        b.setAuthor(sca.nextLine());
        String author = scn.nextLine();
        System.out.println("请输入修改后图书价格:");
//        Scanner scp = new Scanner(System.in);
        String price = scn.nextLine();
//        b.setPrice(scp.nextLine());


        String sql1 = "INSERT INTO `图书管理`(name,author,price,statm)value('"+name1+"','"+author+"','"+price+"','未借出')";
        int i1 = 0;
        try {
            i1 = stmt.executeUpdate(sql1);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (i1 == 0){
            System.out.println("\033[31;6m" + "输入图书名有误，修改失败" + "\033[0m");
//            System.out.println("输入图书名有误，修改失败");
        }else{
            System.out.println("修改成功");
        }
    }
    public static void borrowBook(Statement stmt) throws SQLException {
        System.out.println("请输入要借阅的图书书名:");
        Scanner sc = new Scanner(System.in);
        String name = sc.nextLine();
        String sql = "SELECT * FROM 图书管理 where name = '"+name+"'";
        ResultSet rs = stmt.executeQuery(sql);
        String s1 = "未借出";
        int i1 = 0;
        while (rs.next()){
            String statm = rs.getString("statm");
//            System.out.println(statm);
            if (statm.equals(s1)){
//                System.out.println("借阅成功！请爱惜图书，定时归还");
                String sql1 = "UPDATE 图书管理 SET statm = '借出' where name = '"+name+"'";

//                i1 = stmt.executeUpdate(sql1);
                try {
                    i1 = stmt.executeUpdate(sql1);

                } catch (SQLException e) {
                    e.printStackTrace();
                }finally {
                    rs.close();
                }
                if (i1 == 0){
                    System.out.println("\033[31;6m" + "你输入的图书书名不存在" + "\033[0m");
//                    System.out.println("你输入的图书书名不存在");
                }else{
                    System.out.println("\033[36;6m" + "借阅成功！请爱惜图书，定时归还" + "\033[0m");
//                    System.out.println("借阅成功！请爱惜图书，定时归还");
                    return;
                }
            }else {
                System.out.println("\033[31;4m" + "你想要的图书已经被借阅" + "\033[0m");
//                System.out.println("你想要的图书已经被借阅");
            }


        }


    }


    public static void repayBook(Statement stmt) throws SQLException {
        System.out.println("请输入要归还的图书书名:");
        Scanner sc = new Scanner(System.in);
        String name = sc.nextLine();
        String sql = "SELECT * FROM 图书管理 where name = '"+name+"'";
        ResultSet rs = stmt.executeQuery(sql);
        String s1 = "借出";

        while (rs.next()){
            String statm = rs.getString("statm");
//            System.out.println(statm);
            int i1 = 0;
            if (statm.equals(s1)){
//                System.out.println("借阅成功！请爱惜图书，定时归还");
                String sql1 = "UPDATE 图书管理 SET statm = '未借出' where name = '"+name+"'";

//                i1 = stmt.executeUpdate(sql1);
                try {
                    i1 = stmt.executeUpdate(sql1);

                } catch (SQLException e) {
                    e.printStackTrace();
                }finally {
                    rs.close();
                }
                if (i1 == 0){
                    System.out.println("\033[31;6m" + "输入图书书名有误！" + "\033[0m");
//                    System.out.println("输入图书书名有误！");
                }
                else{
                    System.out.println("\033[36;6m" + "归还成功！欢迎下次光临" + "\033[0m");
//                    System.out.println("归还成功！欢迎下次光临");
                    return;
                }

            }else {
                System.out.println("\033[31;4m" + "你输入的图书已经归还" + "\033[0m");
//                System.out.println("你输入的图书已经归还");
            }

        }
    }
}



